﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class formulaDetails : System.Web.UI.Page
{
    dataAccess da = new dataAccess();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            String formulaID = Request.QueryString[0];
            ShowTableNutrient(formulaID);
            DataSet ds = new DataSet();
            ds = da.getData("SELECT * FROM Formula");
            txtFormulaName.Text = ds.Tables[0].Rows[0]["FormulaName"].ToString();
            txtDescription.Text = ds.Tables[0].Rows[0]["Description"].ToString();
            ds = new DataSet();
            ds = da.getData("SELECT NutrientName FROM Nutrient WHERE NutrientID NOT IN"
                + "(SELECT NutrientID FROM FormulaNutrientItem WHERE FormulaID='" + formulaID + "')");
            ddlNutrientName.DataSource = ds.Tables[0];

            ddlNutrientName.DataValueField = "NutrientName";
            ddlNutrientName.DataBind();
            string sql = "SELECT Unit FROM Nutrient WHERE NutrientName=N'" + ddlNutrientName.Text + "'";
            lbNutrientUnit.Text = da.getData(sql).Tables[0].Rows[0][0].ToString();
        }
        
    }
    void ShowTableNutrient(String formularID)
    {
        string sql = "SELECT n.NutrientName, n.Code, n.Unit, fn.MinValue, fn.MaxValue "
                         + "FROM Formula f, Nutrient n, FormulaNutrientItem fn "
                         + "WHERE	f.FormulaID ='" + formularID + "' "
                        + "AND f.FormulaID = fn.FormulaID AND n.NutrientID= fn.NutrientID";
        DataSet ds = new DataSet();
        ds = da.getData(sql);
        gvFormulaNutrient.DataSource = ds.Tables[0];

        gvFormulaNutrient.DataBind();
        

    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        String formulaID = Request.QueryString[0];
        DataSet ds = new DataSet();
        float min, max;
        try
        {
            min = float.Parse(txtMinValue.Text);
            max = float.Parse(txtMaxValue.Text);
        }catch(Exception ex){
            min = 0;
            max = 0;
        }
        String nutrientID= da.getData("SELECT NutrientID FROM Nutrient WHERE NutrientName='"
            + ddlNutrientName.Text + "'").Tables[0].Rows[0][0].ToString();
        String sql = "INSERT INTO FormulaNutrientItem VALUES ('" + formulaID + "'"
            + ",'" + nutrientID + "',"+min+","+max+")";
        da.executeQuery(sql);
        ShowTableNutrient(formulaID);
    }
    protected void ddlNutrientName_TextChanged(object sender, EventArgs e)
    {
        String sql="SELECT Unit FROM Nutrient WHERE NutrientName='"+ddlNutrientName.Text+"'";
        lbNutrientUnit.Text = da.getData(sql).Tables[0].Rows[0][0].ToString();
        lbNutrientUnit.DataBind();
    }
    protected void ddlNutrientName_SelectedIndexChanged(object sender, EventArgs e)
    {
        String sql = "SELECT Unit FROM Nutrient WHERE NutrientName='" + ddlNutrientName.Text + "'";
        lbNutrientUnit.Text = da.getData(sql).Tables[0].Rows[0][0].ToString();
        lbNutrientUnit.DataBind();
    }
}